﻿Imports Conection
Imports System.Data.SqlClient

Public Class ServicoDAL
    Public OrdCodigo As Integer
    Public VenCodigo As Integer
    Dim View As String
#Region "Incluir"
    Public Function inserir(ByVal pro_descricao As String, pro_precovenda As Decimal, pro_precocusto As Decimal, cat_codigo As Integer, ByVal sta_codigo As Integer, pro_duracao As DateTime, usuario As Integer) As Integer
        Dim cmd = New SqlCommand
        Dim codigo As Integer
        Try
            cmd.CommandText = "insert into Estoque.Gen_Produto(pro_descricao, pro_precocusto,pro_precovenda, cat_codigo,sta_codigo,pro_duracao) values (@descricao,@custo,@venda,@categoria,@status,@duracao)"
            cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar, 250)).Value = pro_descricao
            cmd.Parameters.Add(New SqlParameter("@custo", SqlDbType.Decimal, 8, 2)).Value = pro_precocusto
            cmd.Parameters.Add(New SqlParameter("@venda", SqlDbType.Decimal, 8, 2)).Value = pro_precovenda
            cmd.Parameters.Add(New SqlParameter("@status", SqlDbType.Int)).Value = sta_codigo
            cmd.Parameters.Add(New SqlParameter("@categoria", SqlDbType.Int, 1)).Value = cat_codigo
            cmd.Parameters.Add(New SqlParameter("@duracao", SqlDbType.Time, 4)).Value = pro_duracao.Hour & ":" & pro_duracao.Minute & ":" & pro_duracao.Second
            codigo = objconexao.executacmdAI(cmd, "Gen_Produto")
            objfuncoes.gravaocorrencias("Lançamento de serviços. op: Inserção", codigo, 4, usuario)
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return codigo
    End Function
#End Region

#Region "Alterar"
    Public Function alterar(pro_codigo As Integer, ByVal pro_descricao As String, pro_precovenda As Decimal, pro_precocusto As Decimal, cat_codigo As Integer, ByVal sta_codigo As Integer, pro_duracao As DateTime, usuario As Integer) As Integer
        Dim cmd = New SqlCommand
        Dim codigo As Integer
        Try
            cmd.CommandText = "update Estoque.Gen_Produto set pro_descricao=@descricao,pro_precovenda=@venda, pro_precocusto=@custo,cat_codigo=@categoria ,sta_codigo=@status, pro_duracao=@duracao where pro_codigo=@codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = pro_codigo
            cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar, 250)).Value = pro_descricao
            cmd.Parameters.Add(New SqlParameter("@custo", SqlDbType.Decimal, 8, 2)).Value = pro_precocusto
            cmd.Parameters.Add(New SqlParameter("@venda", SqlDbType.Decimal, 8, 2)).Value = pro_precovenda
            cmd.Parameters.Add(New SqlParameter("@status", SqlDbType.Int)).Value = sta_codigo
            cmd.Parameters.Add(New SqlParameter("@categoria", SqlDbType.Int, 1)).Value = cat_codigo
            cmd.Parameters.Add(New SqlParameter("@duracao", SqlDbType.Time, 4)).Value = pro_duracao.Hour & ":" & pro_duracao.Minute & ":" & pro_duracao.Second
            codigo = objconexao.executaCmd(cmd)
            objfuncoes.gravaocorrencias("Lançamento de serviços. op: Alteração", codigo, 4, usuario)
        Catch ex As Exception
            Throw ex

        Finally
            cmd = Nothing

        End Try
        Return codigo
    End Function
#End Region

#Region "Buscar"
    Function buscar(ByVal descricao As String, tipo As Integer) As DataTable
        Dim cmd As SqlCommand
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            If OrdCodigo > 0 Then
                cmd.Parameters.Add(New SqlParameter("@ordcodigo", SqlDbType.Int)).Value = OrdCodigo
                View = "(select pro_codigo from vw_VenItensOrdem where ord_codigo=@ordcodigo)"
            Else
                cmd.Parameters.Add(New SqlParameter("@ordcodigo", SqlDbType.Int)).Value = VenCodigo
                View = "(select pro_codigo from vw_VenItensVenda where ven_codigo=@ordcodigo)"
            End If
            Select Case tipo
                Case 1
                    'por codigo
                    cmd.CommandText = "select * from Estoque.Gen_Produto where  pro_codigo = @codigo" & _
                        " and pro_codigo Not in" + View
                    cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = descricao
                Case 2
                    'por descricao
                    cmd.CommandText = "select * from vw_EstServico where pro_descricao COLLATE SQL_Latin1_General_CP1_CI_AI  like @descricao" & _
                        " and pro_codigo Not in" + View
                    cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = "%" & descricao & "%"
                Case 3
                    'status
                    cmd.CommandText = "select * from vw_Estservico where sta_codigo=@codigo" & _
                        " and pro_codigo Not in" + View
                    cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = Val(descricao)
                Case Else
                    'todos
                    cmd.CommandText = "select * from vw_EstServico where " & _
                        " pro_codigo Not in" + View
            End Select

            tab = objconexao.executaConsulta(cmd)
            tab.DefaultView.AllowNew = False
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
#End Region

#Region "Verificar Relacionamentos"
    Public Function verificarelacionamento(codigo As Integer) As String
        Dim cmd As New SqlCommand
        Dim dt As New DataTable
        Dim ret As String
        Try
            ret = ""
            'verificar em peças
            cmd.CommandText = "select * from vendas.Ofi_ItensAgendamento where pro_codigo in(select pro_codigo from vw_EstServico where sta_codigo in(1,2)) and pro_codigo=" & codigo
            dt = objconexao.executaConsulta(cmd)
            If (dt.Rows.Count > 0) Then
                ret = "Agendamento"
            End If
            'verificar em ordem serviço / venda
            'cmd = New SqlCommand
            'cmd.CommandText = "select * from vw_EstServico where cat_codigo in (select cat_codigo from vw_EstCategoria where sta_codigo in(1,2)) and cat_codigo=" & codigo
            'dt = objconexao.executaConsulta(cmd)
            'If (dt.Rows.Count > 0) Then
            '    If (ret.Length > 0) Then
            '        ret += ", Venda/Orçamento"
            '    Else : ret = "Venda/Orçamento"
            '    End If
            'End If
        Catch ex As Exception
            Throw New Exception("Erro ao realizar consulta.")
        End Try
        Return ret
    End Function
#End Region
End Class
